﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using CRMEntity;

namespace CRMBiz
{
    public static class DualTemplateHandler
    {
        public static List<Template> templatesListResult = null;
        public static bool collepse = false;

        public static DataTable GetDualTemplates(int DualTemplateId)
        {
            DataTable dt;
            var query = new StringBuilder();
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@DualTemplateId", DualTemplateId)
                };
                query.Append(" SELECT et.EmailTemplate,et.TemplateFormat as Emailformat,st.SMSTemplate,st.TemplateFormat as Smsformat,dt.CategoryId,dt.SmsTemplateID,dt.EmailTemplateID,dt.DualSubject,dt.DualCompany from crm_dualtemplate dt ")
                        .Append(" Left JOIN crm_emailtemplate et ON dt.EmailTemplateID=et.EmailId ")
                        .Append(" Left JOIN crm_smstemplate st ON dt.SmsTemplateID=st.SMSTemplateID where dt.DualTemplateID=@DualTemplateId");
                dt = DBhelper.ExecuteDataset(query.ToString(), para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        //public static DataTable GetDualTemplates1()
        //{
        //    DataTable dt;
        //    var query = new StringBuilder();
        //    try
        //    {
        //        query.Append("Select dt.* ,et.TemplateFormat as EmailTemplateFormat,et.emailsubject,et.EmailTemplate, st.TemplateFormat as SmsTemplateFormat,st.SMSTemplate ")
        //        .Append("FROM crm_dualtemplate dt inner join crm_emailtemplate et on dt.EmailTemplateID=et.EmailId inner join crm_smstemplate st on dt.SmsTemplateID=st.SMSTemplateID ");
        //        dt = DBhelper.ExecuteDataset(query.ToString()).Tables[0];
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return dt;
        //}

        /// <summary>
        /// Get Treeview Nodes
        /// </summary>
        /// <returns></returns>

        public static List<CategoryNode> GetCategoryNodesList(string Company)
        {
            string @setCompany = '%' + @Company + '%';
            TemplateCategory templateCategory = null;
            List<TemplateCategory> templateCategoriesList = new List<TemplateCategory>();
            int currentCategoryId = 0;
            try
            {
                getTemplatesList(Company);
                string sql = "SELECT CategoryId,CategoryName,ParentId FROM crm_dualtemplatecategory where Company like '" + @setCompany + "'";
                DataTable dt = DBhelper.ExecuteDataset(sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    templateCategory = new TemplateCategory();
                    templateCategory.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templateCategory.CategoryName = dr["CategoryName"].ToString().Trim();
                    templateCategory.ParentId = Convert.ToInt16(dr["ParentId"].ToString().Trim());
                    templateCategoriesList.Add(templateCategory);
                }

                var root = from r in templateCategoriesList
                           where r.ParentId == 0
                           select r;

                currentCategoryId = root.First().CategoryId;
                List<CategoryNode> rootnodes = new List<CategoryNode>();
                CategoryNode rootnode = new CategoryNode();
                rootnode.id = currentCategoryId;
                rootnode.text = root.First().CategoryName;
                rootnode.children = GetChildrenNodes(templateCategoriesList, currentCategoryId, collepse);

                List<CategoryNode> rootTemplate = new List<CategoryNode>();
                rootTemplate = GetBottomNodeContents(currentCategoryId);
                //Template childNode = null;
                foreach (CategoryNode category in rootTemplate)
                {
                    rootnode.children.Add(category);
                }
                rootnodes.Add(rootnode);
                return rootnodes;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }

        /// <summary>
        /// Get Treeview Company Nodes
        /// </summary>
        /// <returns></returns>

        public static List<CategoryNode> GetCategoryRoleNodesList(string Company)
        {
            string @setCompany = '%' + @Company + '%';
            TemplateCategory templateCategory = null;
            List<TemplateCategory> templateCategoriesList = new List<TemplateCategory>();
            int currentCategoryId = 0;
            try
            {
                getRoleTemplateList(Company);
                string sql = "SELECT CategoryId,CategoryName,ParentId FROM crm_dualtemplatecategory WHERE Company like '" + setCompany + "'";
                DataTable dt = DBhelper.ExecuteDataset(sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    templateCategory = new TemplateCategory();
                    templateCategory.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templateCategory.CategoryName = dr["CategoryName"].ToString().Trim();
                    templateCategory.ParentId = Convert.ToInt16(dr["ParentId"].ToString().Trim());
                    templateCategoriesList.Add(templateCategory);
                }

                var root = from r in templateCategoriesList
                           where r.ParentId == 0
                           select r;

                currentCategoryId = root.First().CategoryId;
                List<CategoryNode> rootnodes = new List<CategoryNode>();
                CategoryNode rootnode = new CategoryNode();
                rootnode.id = currentCategoryId;
                rootnode.text = root.First().CategoryName;
                rootnode.children = GetChildrenNodes(templateCategoriesList, currentCategoryId, collepse);

                List<CategoryNode> rootTemplate = new List<CategoryNode>();
                rootTemplate = GetBottomNodeContents(currentCategoryId);
                //Template childNode = null;
                foreach (CategoryNode category in rootTemplate)
                {
                    rootnode.children.Add(category);
                }
                rootnodes.Add(rootnode);
                return rootnodes;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get templates list
        /// </summary>
        /// <param name="templateTableName"></param>
        private static void getTemplatesList(string Company)
        {
            string @setCompany = '%' + @Company + '%';
            Template template = null;
            templatesListResult = new List<Template>();
            string sbSql = null;
            try
            {
                sbSql = "Select DualTemplateID,DualTemplate,CategoryId From crm_dualtemplate where DualCompany like '" + @setCompany + "'";
                DataTable dualTemplateTable = DBhelper.ExecuteDataset(sbSql).Tables[0];
                foreach (DataRow dr in dualTemplateTable.Rows)
                {
                    template = new Template();
                    template.TemplateId = Convert.ToInt16(dr["DualTemplateID"].ToString().Trim());
                    template.TemplateTitle = dr["DualTemplate"].ToString().Trim();
                    template.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templatesListResult.Add(template);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        /// <summary>
        /// Get temlates list according to user Company
        /// </summary>
        /// <param name="templateTableName"></param>
        private static void getRoleTemplateList(string Company)
        {
            Template template = null;
            templatesListResult = new List<Template>();
            DataTable dualTemplateTable = null;
            try
            {
                dualTemplateTable = DBhelper.ExecuteDataset(string.Format("Select DualTemplateID,DualTemplate,CategoryId From crm_dualtemplate where DualCompany like '%{0}%'", Company)).Tables[0];
                foreach (DataRow dr in dualTemplateTable.Rows)
                {
                    template = new Template();
                    template.TemplateId = Convert.ToInt16(dr["DualTemplateID"].ToString().Trim());
                    template.TemplateTitle = dr["DualTemplate"].ToString().Trim();
                    template.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templatesListResult.Add(template);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get children Nodes
        /// </summary>
        /// <param name="categoriesList"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetChildrenNodes(List<TemplateCategory> categoriesList, int parentId, bool isCollepse)
        {
            List<CategoryNode> childrenNodesList = new List<CategoryNode>();
            CategoryNode childNode = null;
            int currentCategoryId = 0;
            try
            {
                var childrenCategories = from r in categoriesList
                                         where r.ParentId == parentId
                                         select r;

                foreach (TemplateCategory category in childrenCategories)
                {
                    childNode = new CategoryNode();
                    currentCategoryId = category.CategoryId;
                    childNode.id = currentCategoryId;
                    childNode.text = category.CategoryName;

                    if (isCollepse == true)
                        childNode.state = "closed";
                    else
                        childNode.state = "open";

                    childNode.children = GetChildrenNodes(categoriesList, category.CategoryId, true);

                    childrenNodesList.Add(childNode);
                }

                foreach (CategoryNode category in childrenNodesList)
                {
                    List<CategoryNode> rootTemplate = new List<CategoryNode>();
                    rootTemplate = GetBottomNodeContents(category.id);

                    foreach (CategoryNode template in rootTemplate)
                    {
                        category.children.Add(template);
                    }
                    if (category.children.Count == 0)
                    {
                        category.id = -1;
                        category.state = "open";
                        category.iconCls = "icon-folder";
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return childrenNodesList;
        }
        /// <summary>
        /// Get contents of bottom Node
        /// </summary>
        /// <param name="currentCategoryId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetBottomNodeContents(int currentCategoryId)
        {
            List<CategoryNode> bottomNodesList = new List<CategoryNode>();
            CategoryNode node = null;
            try
            {
                var result = from e in templatesListResult
                             where e.CategoryId == currentCategoryId
                             select e;

                foreach (Template e in result)
                {
                    node = new CategoryNode();
                    node.id = e.TemplateId;
                    node.text = e.TemplateTitle;
                    bottomNodesList.Add(node);
                }

                return bottomNodesList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int saveDualTemplate(int DualTemplateID, string DualSubject, string DualTemplate, int SmsTemplateID, int EmailTemplateID, int CategoryID, string DualCompany)
        {
            int intReturnValue = 0;
            //bool flag = false;
            try
            {
                var para = new[] {
                     DBhelper.SetParameter("@DualTemplateID", DualTemplateID),
                     DBhelper.SetParameter("@DualSubject",DualSubject),
                     DBhelper.SetParameter("@DualTemplate", DualTemplate),
                     DBhelper.SetParameter("@DualCompany",DualCompany),
                     DBhelper.SetParameter("@SmsTemplateID",SmsTemplateID),
                     DBhelper.SetParameter("@EmailTemplateID",EmailTemplateID),
                     DBhelper.SetParameter("@CategoryID",CategoryID),
                };

                if (DualTemplateID <= 0)
                {
                    DataSet ds = DBhelper.ExecuteDataset("Select * From crm_dualtemplate Where DualTemplate = @DualTemplate", para);
                    if (ds.Tables[0].Rows.Count > 0)
                        intReturnValue = 0;
                    else
                    {
                        DBhelper.ExecuteNonQuery("Insert into crm_dualtemplate (DualTemplate,DualSubject,SmsTemplateID,EmailTemplateID,CategoryId,DualCompany) values (@DualTemplate ,@DualSubject,@SmsTemplateID,@EmailTemplateID,@CategoryId,@DualCompany)", para);
                        intReturnValue = 1;
                    }
                }
                else
                {
                    DBhelper.ExecuteNonQuery("Update crm_dualtemplate SET DualTemplate=@DualTemplate,DualSubject=@DualSubject,SmsTemplateID=@SmsTemplateID,EmailTemplateID=@EmailTemplateID, CategoryID=@CategoryID,DualCompany=@DualCompany where DualTemplateID=@DualTemplateID", para);
                    intReturnValue = 1;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intReturnValue;
        }

        public static bool deleteDualTemplate(int DualTemplateID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                     DBhelper.SetParameter("@DualTemplateID", DualTemplateID)
                 };
                DBhelper.ExecuteNonQuery("Delete from crm_dualtemplate where DualTemplateID=@DualTemplateID");
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// function to get dual email list
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns>DualList</returns>
        public static DataSet GetDualTemplateList(int pageIndex, int pageSize, string Company, int CategoryId)
        {

            DataSet ds;
            try
            {
                var sbSql = new StringBuilder("SELECT DualTemplateID,DualTemplate,DualCompany,DualSubject,(")
                .AppendFormat("Select count(*) from crm_dualtemplate where DualCompany LIKE '%{0}%' AND CategoryId={1}) as Total from crm_dualtemplate where DualCompany LIKE '%{0}%' AND CategoryId={1} order by DualTemplate", Company, CategoryId);
                sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                ds = DBhelper.ExecuteDataset(sbSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to Delete listData
        /// </summary>
        /// <param name="EmailId"></param>   
        /// <returns>true/false</returns>
        public static bool DeleteDualListData(int DualTemplateID)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@DualTemplateID", DualTemplateID),
                };

                string sbSql = "delete from crm_dualtemplate where DualTemplateID=@DualTemplateID";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
